from openpyxl import Workbook
from openpyxl import load_workbook
# 到此我的想要的 基本可以解决了
source_file_path = r'D:\Desktop\exceltest\部门表待处理(新).xlsx'
target_file_path = r'D:\Desktop\exceltest\区县部门.xlsx'
source_sheet_name = '3级区县部门'
source_wb = load_workbook(source_file_path)
target_wb = load_workbook(target_file_path)
source_ws = source_wb[source_sheet_name]
source_set = set()

source_row_num = 2
source_col_num = 19
source_cell = source_ws.cell(source_row_num, source_col_num)
while source_cell.value is not None:
    val = source_cell.value
    source_set.add(val[0: 6])
    source_row_num += 1
    source_cell = source_ws.cell(source_row_num, source_col_num)

print(f'sheet{source_ws.title}的有效行数为:{source_row_num-1}')


# 将不等于header的删掉
for name in target_wb.sheetnames:
    if name != 'header':
        del target_wb[name]

for item in source_set:
    new_sheet = target_wb.copy_worksheet(target_wb['header'])
    new_sheet.title = item

for name in target_wb.sheetnames:
    if name != 'header':
        target_ws = target_wb[name]
        target_row_num = 3
        for i in range(2, source_row_num):
            if name == source_ws.cell(i, source_col_num).value[0:6]:
                target_ws[f'A{target_row_num}'].value = source_ws[f'J{i}'].value
                target_ws[f'B{target_row_num}'].value = source_ws[f'J{i}'].value
                target_ws[f'C{target_row_num}'].value = source_ws[f'J{i}'].value[7:18]
                target_ws[f'D{target_row_num}'].value = source_ws[f'C{i}'].value
                target_ws[f'E{target_row_num}'].value = source_ws[f'D{i}'].value
                target_ws[f'F{target_row_num}'].value = '部门'
                target_ws[f'K{target_row_num}'].value = '0'
                target_ws[f'L{target_row_num}'].value = '0'
                target_ws[f'M{target_row_num}'].value = '0'
                target_ws[f'N{target_row_num}'].value = '0'
                target_ws[f'O{target_row_num}'].value = '0'
                target_ws[f'T{target_row_num}'].value = '1'
                target_row_num += 1

target_wb.save(target_file_path)
print('完成')
